package cn.testfan.apitest.excel;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JdbcDataUtil {

	public static Connection getConnection(String url, String username, String password) {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void closeConnection(Connection conn) {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		Connection conn = getConnection("jdbc:mysql://101.200.167.51:3306/api?useUnicode=true&characterEncoding=utf8",
				"testfan", "Testfan001");
		getData(conn, "select username,password from oschina_user");
		closeConnection(conn);
	}

	public static Object[][] getData(Connection conn, String query) {
		Object[][] data = null;

		try {
			PreparedStatement ps;
			ps = conn.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			int column = meta.getColumnCount();

			List<Object[]> list = new ArrayList<>();
			while (rs.next()) {
				Object[] oa = new Object[column];
				for (int i = 1; i <= column; i++) {
					oa[i - 1] = rs.getObject(i);
				}
				list.add(oa);
			}
			if (list.size() > 0) {
				data = new Object[list.size()][];
				for (int i = 0; i < list.size(); i++) {
					data[i] = list.get(i);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return data;
	}

}
